﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OleDb;
using Home.Utils;
using NhanAi.Entity;
using NhanAi.App_Code;
namespace NhanAi.DAO
{
    public class TaxonomyDao : DBConnection
    {
        public void Create(Taxonomy t)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmTaxonomy");
            try
            {
                if (Connect())
                {
                    //  nb.AddValueField("TermId", t.TermId);
                    nb.AddValueField("Taxonomy", t.TaxonomyType);
                    nb.AddValueField("Description", t.Description);
                    nb.AddValueField("DescriptionEn", t.DescriptionEn);
                    nb.AddValueField("ParentId", t.ParentId);
                    nb.AddValueField("ItemCount", t.Count);
                    nb.AddValueField("TermName", t.TermName);
                    nb.AddValueField("TermNameEN", t.TermNameEn);
                    OleDbCommand cmd = nb.GetCreateCommand(conn);

                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "select @@IDENTITY";
                    t.Id = (int)cmd.ExecuteScalar();
                    Disconnect();
                }
            }
            catch (OleDbException ex) { throw ex; }
        }
        public void Update(Taxonomy t)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmTaxonomy");
            if (Connect())
            {

                try
                {
                    nb.AddValueField("TermId", t.TermId);
                    nb.AddValueField("Taxonomy", t.TaxonomyType);
                    nb.AddValueField("Description", t.Description);
                    nb.AddValueField("DescriptionEn", t.DescriptionEn);
                    nb.AddValueField("ParentId", t.ParentId);
                    nb.AddValueField("ItemCount", t.Count);
                    nb.AddValueField("TermName", t.TermName);
                    nb.AddValueField("TermNameEN", t.TermNameEn);
                    nb.AddConditionField("ID", t.Id);
                    OleDbCommand cmd = nb.GetUpdateCommand(conn, true);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex) { throw ex; }
                Disconnect();

            }

        }
        public void Delele(int uid)
        {
            NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmTaxonomy");
            if (Connect())
            {

                nb.AddConditionField("ID", uid);
                OleDbCommand cmd = nb.GetDeleteCommand(conn, true);

                Disconnect();
            }
        }
        public void Delete(string ids)
        {
          //  NonQueryCommandBuilder nb = new NonQueryCommandBuilder("HmTaxonomy");
            OleDbCommand cmd = new OleDbCommand("delete from HmTaxonomy where Id in ("+ids+")",conn);
            if (Connect())
            {
                cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
       
        public Taxonomy Read(int id, int lang)
        {
            Taxonomy t = null;
            if (Connect())
            {
                OleDbCommand cmd = new OleDbCommand("select * from HmTaxonomy where ID=@id", conn);
                cmd.Parameters.AddWithValue("@id", id);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    t = new Taxonomy();
                    t.Id = dr.GetInt32(0);
                    t.TermId = dr.GetInt32(1);
                    t.TaxonomyType = dr.GetString(2);
                    t.Description = dr.GetString(3);
                    t.DescriptionEn = dr.GetString(4);
                    t.ParentId = dr.GetInt32(5);
                    t.Count = dr.GetInt32(6);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI)) t.TermName = dr.GetString(7);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN)) t.TermNameEn = dr.GetString(8);

                }
                Disconnect();
            }
            return t;
        }
        public Taxonomy Read(string name, int lang)
        {
            Taxonomy t = null;
            if (Connect())
            {
                OleDbCommand cmd = new OleDbCommand("select * from HmTaxonomy where Description=@name", conn);
                cmd.Parameters.AddWithValue("@name", name);
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    t = new Taxonomy();
                    t.Id = dr.GetInt32(0);
                    t.TermId = dr.GetInt32(1);
                    t.TaxonomyType = dr.GetString(2);
                    t.Description = dr.GetString(3);
                    t.DescriptionEn = dr.GetString(4);
                    t.ParentId = dr.GetInt32(5);
                    t.Count = dr.GetInt32(6);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI)) t.TermName = dr.GetString(7);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN)) t.TermNameEn = dr.GetString(8);

                }
                Disconnect();
            }
            return t;
        }
        public List<Taxonomy> ReadMany(int pageSize, int pageNum,int lang, int termId, string taxonomy, int parentId, string description, out int total)
        {
            List<Taxonomy> list = new List<Taxonomy>();
            OleDbCommand cmd = new OleDbCommand("select count(*) from HmTaxonomy", conn);
            total = 0;
            if (Connect())
            {
                total = (int)cmd.ExecuteScalar();
                QueryCommandBuilder qb = new QueryCommandBuilder("select TOP #pageSize * from (select TOP #total * from HmTaxonomy where 1=1");
                qb.AddSuffix("order by Id desc) order by Id asc");
                qb.AddParam("pageSize", pageSize);
                int tmp = total - (pageNum - 1) * pageSize;
                qb.AddParam("total", tmp > 0 ? tmp : 1);
                if (termId > 0) qb.AddWithValue("TermId", termId);
                if (!String.IsNullOrEmpty(taxonomy)) qb.AddWithValue("Taxonomy", taxonomy);
                if (parentId > 0) qb.AddWithValue("ParentId", parentId);
                if (!String.IsNullOrEmpty(description))
                {
                    qb.AddWithValue("Description", description, "like");
                    qb.AddWithValue("Description", description, "like", "OR");
                }

                cmd = qb.GetBuildedCommand(conn);

                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Taxonomy t = new Taxonomy();
                    t.Id = dr.GetInt32(0);
                    t.TermId = dr.GetInt32(1);
                    t.TaxonomyType = dr.GetString(2);
                    t.TaxonomyType = dr.GetString(2);
                    t.Description = dr.GetString(3);
                    t.DescriptionEn = dr.GetString(4);
                    t.ParentId = dr.GetInt32(5);
                    t.Count = dr.GetInt32(6);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_VI)) t.TermName = dr.GetString(7);
                    if (NAConstants.HasFlag(lang, NAConstants.LANGUAGE_EN)) t.TermNameEn = dr.GetString(8);
                    list.Add(t);
                }
            }
           
            return list;
        }
        public List<Taxonomy> ReadAll()
        {
            List<Taxonomy> list = new List<Taxonomy>();
            OleDbCommand cmd = new OleDbCommand("select Id, TermName,TermNameEn, ParentId,ItemCount from HmTaxonomy where Taxonomy=@taxonomy", conn);
           cmd.Parameters.AddWithValue("@taxonomy","taxonomy");
            if (Connect())
            {             
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Taxonomy t = new Taxonomy();
                    t.Id = dr.GetInt32(0);                 
                    t.TermName = dr.GetString(1);
                    t.TermNameEn = dr.GetString(2);
                    t.ParentId = dr.GetInt32(3);
                    t.Count = dr.GetInt32(4);
                   
                    list.Add(t);
                }
            }
            return list;
        }
        public List<Taxonomy> ReadAll(int parentId)
        {
            List<Taxonomy> list = new List<Taxonomy>();
            OleDbCommand cmd = new OleDbCommand("select Id, TermName,TermNameEn, ParentId,ItemCount from HmTaxonomy where Taxonomy=@taxonomy and ParentId=@parentId", conn);
            cmd.Parameters.AddWithValue("@taxonomy", "taxonomy");
            cmd.Parameters.AddWithValue("@parentId", parentId);
            if (Connect())
            {
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Taxonomy t = new Taxonomy();
                    t.Id = dr.GetInt32(0);
                    //  t.TermId = dr.GetInt32(1);
                    t.TermName = dr.GetString(1);
                    t.TermNameEn = dr.GetString(2);
                    t.ParentId = dr.GetInt32(3);
                    t.Count = dr.GetInt32(4);

                    list.Add(t);
                }
            }
            return list;
        }
        public List<Taxonomy> ReadByPost(int postId)
        {
            List<Taxonomy> list = new List<Taxonomy>();
            OleDbCommand cmd = new OleDbCommand("select a.Id, a.TermName,a.TermNameEn, a.ParentId, a.ItemCount from HmTaxonomy a inner join HmTaxonomyRelationship b on a.Id=b.TaxonomyId where a.Taxonomy=@taxonomy and b.ObjectId=@postId", conn);
            cmd.Parameters.AddWithValue("@taxonomy", "taxonomy");
            cmd.Parameters.AddWithValue("@postId", postId);
            if (Connect())
            {
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Taxonomy t = new Taxonomy();
                    t.Id = dr.GetInt32(0);
                    //  t.TermId = dr.GetInt32(1);
                    t.TermName = dr.GetString(1);
                    t.TermNameEn = dr.GetString(2);
                    t.ParentId = dr.GetInt32(3);
                    t.Count = dr.GetInt32(4);

                    list.Add(t);
                }
            }
            return list;
        }
    }
}